package com.example.myaccount.database;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.util.Log;

import androidx.annotation.RequiresApi;

import com.example.myaccount.R;
import com.example.myaccount.activity.MainActivity;
import com.example.myaccount.bean.DailyStatistics;
import com.example.myaccount.bean.Flow;
import com.example.myaccount.constant.MyConst;
import com.example.myaccount.util.ResourceUtil;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.Map;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static DatabaseHelper databaseHelper;
    /**
     * 数据库版本，需要升级数据库时只要加一即可
     */
    private static final int DATABASE_VERSION = 1;

    /**
     * 数据库名
     */
    private static final String DATABASE_NAME = "mySQLite.db";

    private static final String TABLE_NAME = "account";
    private static final String COLUMN_TYPE = "type";
    private static final String COLUMN_DATE = "date";
    private static final String COLUMN_AMOUNT = "amount";
    private static final String COLUMN_ANNOTATION = "annotation";

    /**
     * 构造方法
     * 每次创建DatabaseHelper对象时，若本应用无该数据库，则新建数据库并调用onCreate方法；
     * 若该数据库已创建则直接使用已存在的数据库且跳过onCreate方法
     *
     * @param context 上下文
     */
    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static DatabaseHelper getDefaultDatabaseHelper(Context context){
        if(databaseHelper == null){
            databaseHelper = new DatabaseHelper(context);
            return databaseHelper;
        }else{
            return databaseHelper;
        }
    }
    /**
     * 创建数据库是时调用（只被调用一次）
     *
     * @param db 数据库
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
                COLUMN_TYPE + " TEXT NOT NULL," +
                COLUMN_DATE + " TEXT NOT NULL," +
                COLUMN_AMOUNT + " DOUBLE NOT NULL, " +
                COLUMN_ANNOTATION + " TEXT )");
    }

    /**
     * 更新数据库时调用
     *
     * @param db         数据库
     * @param oldVersion 旧版本号
     * @param newVersion 新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //升级：往user表，添加性别属性
        //db.execSQL("ALTER TABLE user ADD COLUMN gender VARCHAR(2)");
    }

    /**
     * 插入一条数据
     *
     * @param type   金额类型
     * @param date   日期
     * @param amount 金额
     */
    public void insert(String type, String date, double amount, String annotation) {
        SQLiteDatabase database = getWritableDatabase();
        //向user表插入一条数据
        database.execSQL("INSERT INTO " + TABLE_NAME + "(" +
                COLUMN_TYPE + "," +
                COLUMN_DATE + "," +
                COLUMN_AMOUNT + "," +
                COLUMN_ANNOTATION + ") VALUES(?,?,?,?)", new Object[]{type, date, amount, annotation});
    }

    /**
     * 获得日期的字符串列表
     * @return 日期的字符串ArrayList
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public ArrayList<String> getDateList() {
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> dateList = new ArrayList<>();
        //获得日期列表
        Cursor cursor = database.rawQuery("SELECT DISTINCT " + COLUMN_DATE + " FROM " + TABLE_NAME, new String[]{});
        //使用moveToNext()逐条读取
        while (cursor.moveToNext()) {
            @SuppressLint("Range") String data = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
            dateList.add(data);
        }
        cursor.close();
        dateList = sortDateList(dateList);
//        Log.d(MyConst.TAG,dateList.toString());
        return dateList;
    }

    @RequiresApi(api = Build.VERSION_CODES.N)
    public ArrayList<String> sortDateList(ArrayList<String> dateList){
        // 设定自己的比较方式
        // 1顺序 -1倒序
//        dateList.sort(new Comparator<String>() {
//            @Override
//            public int compare(String s1, Integer s2) {
//                int i = s1 > s2 ? 1 : -1;
////                System.out.println("开始比较 [o1] - " + o1 + "\t [o2] - " + o2);
//                return i;
//            }
//        });
        dateList.sort(new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {
                int i = o1.compareTo(o2)*-1;
                return i;
            }
        });
        return dateList;
//        ArrayList<String> newDateList = new ArrayList<>();
//        for(String date:dateList){
//            String dateSplit[] = date.split("_");
//
//        }
    }
    /**
     * 获得表的每日统计数据
     * @return 每日统计数据ArrayList
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public ArrayList<DailyStatistics> getDialyStatisticsList() {
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<DailyStatistics> dailyStatistics = new ArrayList<>();
        ArrayList<String> dateList = getDateList();
//        Log.d(MyConst.TAG,dateList.toString());
        if(dateList==null)
            return null;
        for (String date : dateList) {
            //获得日期列表
            Cursor cursor = database.rawQuery("SELECT " + COLUMN_AMOUNT + " FROM " + TABLE_NAME + " WHERE " + COLUMN_DATE + " = '" + date + "';", new String[]{});
            ArrayList<Double> amountList = new ArrayList<>();
            //使用moveToNext()逐条读取
            while (cursor.moveToNext()) {
                @SuppressLint("Range") double data = cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT));
                amountList.add(data);
            }
            double dailyIncome = 0d;
            double dailyOutcome = 0d;
            for (double amount : amountList) {
                if (amount > 0) {
                    dailyIncome += amount;
                } else if (amount < 0) {
                    dailyOutcome += amount;
                }
            }
            cursor.close();
            dailyStatistics.add(new DailyStatistics(date,dailyIncome,dailyOutcome));
        }
        return dailyStatistics;
    }

    /**
     * 获得所有的每日流水
     * @return 所有的每日流水列表
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public ArrayList<ArrayList<Flow>> getTotalDailyFlow(){
        ArrayList<ArrayList<Flow>> totalDailyFlow = new ArrayList<>();
        ArrayList<String> dateList = getDateList();
        if(dateList==null)
            return null;
        for(String date:dateList){
            ArrayList<Flow> dailyFlow = getDailyFlow(date);
            totalDailyFlow.add(dailyFlow);
        }
        return totalDailyFlow;
    }

    /**
     * 获得每日流水
     * @param date 日期
     * @return 每日流水ArrayList
     */
    public ArrayList<Flow> getDailyFlow(String date) {
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<Flow> flowList = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_DATE + " = '" + date + "';", new String[]{});
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));
            @SuppressLint("Range") String type = cursor.getString(cursor.getColumnIndex(COLUMN_TYPE));
            @SuppressLint("Range") double amount = cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT));
            @SuppressLint("Range") String annotation = cursor.getString(cursor.getColumnIndex(COLUMN_ANNOTATION));
            flowList.add(new Flow(id,type,date,annotation,amount));
        }
        cursor.close();
        return flowList;
    }

    /**
     * 获得净资产
     * @return 净资产的double值
     */
    public double getNetAssets(){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<Double> amountList = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT "+COLUMN_AMOUNT+" FROM " + TABLE_NAME , new String[]{});
        while (cursor.moveToNext()) {
            @SuppressLint("Range") double amount = cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT));
            amountList.add(amount);
        }
        cursor.close();
        double total=0;
        for(double amount:amountList){
            total+=amount;
        }
        return total;
    }

    /**
     * 获得月收入
     * @param year 年份
     * @param month 月份
     * @return
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public double getMonthlyIncome(String year, String month){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> dateList = getDateList();
        ArrayList<String> monthlyDateList = new ArrayList<>();
        for(String date:dateList){
            if(date.contains(year)&&date.contains(month)){
                monthlyDateList.add(date);
            }
        }
        ArrayList<Double> amountList = new ArrayList<>();
        for(String date:monthlyDateList){
            Cursor cursor = database.rawQuery("SELECT "+COLUMN_AMOUNT+" FROM " + TABLE_NAME +" WHERE "+COLUMN_DATE+" = '"+date+"';", new String[]{});
            while (cursor.moveToNext()) {
                @SuppressLint("Range") double amount = cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT));
                if(amount>0)
                    amountList.add(amount);
            }
            cursor.close();
        }
        double total=0;
        for(double amount:amountList){
            total+=amount;
        }
        return total;
    }

    /**
     * 获得月支出
     * @param year 年份
     * @param month 月份
     * @return
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public double getMonthlyOutcome(String year, String month){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> dateList = getDateList();
        ArrayList<String> monthlyDateList = new ArrayList<>();
        for(String date:dateList){
            if(date.contains(year)&&date.contains(month)){
                monthlyDateList.add(date);
            }
        }
        ArrayList<Double> amountList = new ArrayList<>();
        for(String date:monthlyDateList){
            Cursor cursor = database.rawQuery("SELECT "+COLUMN_AMOUNT+" FROM " + TABLE_NAME +" WHERE "+COLUMN_DATE+" = '"+date+"';", new String[]{});
            while (cursor.moveToNext()) {
                @SuppressLint("Range") double amount = cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT));
                if(amount<0)
                    amountList.add(amount);
            }
            cursor.close();
        }
        double total=0;
        for(double amount:amountList){
            total+=amount;
        }
        return total;
    }

    /**
     * 更新流水
     * @param id 流水id
     * @param type 流水类型
     * @param date 流水日期
     * @param amount 流水金额
     * @param annotation 流水注释
     * @return 是否操作成功
     */
    public boolean updateFlow(int id,String type,String date,double amount,String annotation){
        SQLiteDatabase database = getWritableDatabase();
        try {
            database.execSQL("UPDATE " + TABLE_NAME + " SET " +
                    COLUMN_TYPE + " = '"+type+"'," +
                    COLUMN_DATE + " = '"+date+"'," +
                    COLUMN_AMOUNT + " = "+amount+"," +
                    COLUMN_ANNOTATION + " = '"+annotation+"' WHERE id = " + id);
//            Log.d(MyConst.TAG,"更新成功");
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 删除流水
     * @param id 流水id
     * @return 是否操作成功
     */
    public boolean deleteFlow(int id){
        SQLiteDatabase database = getWritableDatabase();
        try {
            //向user表插入一条数据
            database.execSQL("DELETE FROM " + TABLE_NAME + "  WHERE id = " + id);
//            Log.d(MyConst.TAG,"删除成功");
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 获得收入月Map
     * @param year 年份
     * @param month 月份
     * @return
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public Map<String,Double> getIncomeTypeMonthlyMap(String year, String month){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> monthlyDateList = getMonthlyDateList(year,month);
        ArrayList<String> typeList = new ArrayList<>();
        for(String date:monthlyDateList){
            Cursor cursor = database.rawQuery("SELECT DISTINCT "+COLUMN_TYPE+" FROM " + TABLE_NAME +" WHERE "+COLUMN_DATE+" = '"+date+"';", new String[]{});
            while (cursor.moveToNext()) {
                @SuppressLint("Range") String type = cursor.getString(cursor.getColumnIndex(COLUMN_TYPE));
                if(MyConst.SETTINGS_TYPES_INCOME_ID_DEFAULT.contains(type))
                    typeList.add(type);
            }
            cursor.close();
        }
        ArrayList<Flow> monthlyFlowList = getMonthlyFlowList(year,month);
        ArrayList<Double> amountList = new ArrayList<>();
        for(String type:typeList){
            double typeTotal=0;
            for(Flow flow:monthlyFlowList){
                if(flow.getType().equals(type)){
                    if(flow.getAmount()>0)
                        typeTotal+=flow.getAmount();
                }
            }
            amountList.add(typeTotal);
        }
        Map<String, Double> dataMap = new LinkedHashMap<>();
        for(int i=0;i<typeList.size();i++){
            String typeStr = MainActivity.getContext().getString(ResourceUtil.getResId("type_"+typeList.get(i), R.string.class))+" "+new Double(Math.abs(amountList.get(i))).toString();
            dataMap.put(typeStr,amountList.get(i));
        }
        return dataMap;
    }

    /**
     * 获得支出月Map
     * @param year 年份
     * @param month 月份
     * @return
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public Map<String,Double> getOutcomeTypeMonthlyMap(String year, String month){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> monthlyDateList = getMonthlyDateList(year,month);
        ArrayList<String> typeList = new ArrayList<>();
        for(String date:monthlyDateList){
            Cursor cursor = database.rawQuery("SELECT DISTINCT "+COLUMN_TYPE+" FROM " + TABLE_NAME +" WHERE "+COLUMN_DATE+" = '"+date+"';", new String[]{});
            while (cursor.moveToNext()) {
                @SuppressLint("Range") String type = cursor.getString(cursor.getColumnIndex(COLUMN_TYPE));
                if(MyConst.SETTINGS_TYPES_OUTCOME_ID_DEFAULT.contains(type))
                    typeList.add(type);
            }
            cursor.close();
        }
        ArrayList<Flow> monthlyFlowList = getMonthlyFlowList(year,month);
        ArrayList<Double> amountList = new ArrayList<>();
        for(String type:typeList){
            double typeTotal=0;
            for(Flow flow:monthlyFlowList){
                if(flow.getType().equals(type)){
                    if(flow.getAmount()<0)
                        typeTotal+=flow.getAmount();
                }
            }
            amountList.add(typeTotal);
        }
        // 模拟数据
        Map<String, Double> dataMap = new LinkedHashMap<>();
        for(int i=0;i<typeList.size();i++){
            String typeStr = MainActivity.getContext().getString(ResourceUtil.getResId("type_"+typeList.get(i), R.string.class))+" "+new Double(Math.abs(amountList.get(i))).toString();
            dataMap.put(typeStr,amountList.get(i));
        }
        return dataMap;
    }
    /**
     * 获得以月为单位的流水列表
     * @param year 年份
     * @param month 月份
     * @return 月流水ArrayList
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public ArrayList<Flow> getMonthlyFlowList(String year, String month){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> monthlyDateList = getMonthlyDateList(year,month);
        ArrayList<Flow> flowList = new ArrayList<>();
        for(String date:monthlyDateList){
            Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_DATE + " = '" + date + "';", new String[]{});
            while (cursor.moveToNext()) {
                @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));
                @SuppressLint("Range") String type = cursor.getString(cursor.getColumnIndex(COLUMN_TYPE));
                @SuppressLint("Range") double amount = cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT));
                @SuppressLint("Range") String annotation = cursor.getString(cursor.getColumnIndex(COLUMN_ANNOTATION));
                flowList.add(new Flow(id,type,date,annotation,amount));
            }
            cursor.close();
        }
        return flowList;
    }

    /**
     * 获得月流水的日期列表
     * @param year 年份
     * @param month 月份
     * @return 月流水的日期字符串ArrayList
     */
    @RequiresApi(api = Build.VERSION_CODES.N)
    public ArrayList<String> getMonthlyDateList(String year, String month){
        SQLiteDatabase database = getReadableDatabase();
        ArrayList<String> dateList = getDateList();
        ArrayList<String> monthlyDateList = new ArrayList<>();
        for(String date:dateList){
            if(date.contains(year)&&date.contains(month)){
                monthlyDateList.add(date);
            }
        }
        return monthlyDateList;
    }
}